-- @author lixiyong01
-- @date 2023.02.24
-- 进行OLAP层分析提存处理

use hdp_lbg_supin_zplisting;

insert overwrite table app_zp_wb_n_flow_infoid partition(dt='${dateSuffix}')
select

    platform,
    slot,
    info_id,
    info_type,
    info_local1,
    info_cate2,
    info_cate3,



    sum(has_exposure) as exp_cnt,
    sum(has_infodetail) as click_cnt,
    sum(if(bc_type='C', has_im, 0)) as c_im_cnt,
    sum(if(bc_type='B', has_im, 0)) as b_im_cnt,
    sum(has_delivery) as delivery_cnt,
    sum(has_tel) as tel_cnt,
    sum(if(tel_conn_cnt>0, 1, 0)) as tel_conn_cnt
    sum(yx_cost) as yx_cost,
    sum(yx_cash) as yx_cash,
    sum(jz_cost) as jz_cost,
    sum(jz_cash) as jz_cash,

    max(info_buid) as info_buid,
    max(info_buid_type) as info_buid_type,
    max(info_etpid) as info_etpid,
    info_title,
    max(concat_ws(',',info_types) as info_types,

from hdp_lbg_supin_zplisting.dm_zp_wb_n_flow
where dt='${#date(0,0,-1):yyyyMMdd#}'
    and blackiptag=0
    and spidertag=0
    and userid>0
    and (has_exposure+has_infodetail+has_im+has_tel+has_delivery)>0;

